package com.example.books2.config;

import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class DatabaseInitializer {

    @Bean
    @Order(1)  // 确保在数据初始化之前运行
    public CommandLineRunner initDatabase(JdbcTemplate jdbcTemplate) {
        return args -> {
            try {
                System.out.println("开始初始化数据库存储过程...");
                
                // 删除已存在的存储过程和触发器
                jdbcTemplate.execute("DROP PROCEDURE IF EXISTS borrow_book");
                jdbcTemplate.execute("DROP TRIGGER IF EXISTS after_return_book");
                System.out.println("已删除旧的存储过程和触发器");

                // 创建借书存储过程
                String createProcedure = """
                    CREATE PROCEDURE borrow_book(
                        IN p_book_id BIGINT,
                        IN p_user_id BIGINT,
                        OUT p_success INT
                    )
                    BEGIN
                        DECLARE book_is_available BOOLEAN;
                        
                        -- 检查图书是否存在且可借
                        SELECT available INTO book_is_available
                        FROM books 
                        WHERE id = p_book_id;
                        
                        IF book_is_available IS NULL THEN
                            SET p_success = 0;
                        ELSEIF book_is_available = FALSE THEN
                            SET p_success = 0;
                        ELSE
                            -- 更新图书状态
                            UPDATE books SET available = FALSE 
                            WHERE id = p_book_id;
                            
                            -- 创建借阅记录
                            INSERT INTO borrow_records (book_id, user_id, borrow_date, returned) 
                            VALUES (p_book_id, p_user_id, NOW(), FALSE);
                            
                            SET p_success = 1;
                        END IF;
                    END
                """;
                jdbcTemplate.execute(createProcedure);
                System.out.println("已创建借书存储过程");

                // 创建还书触发器
                String createTrigger = """
                    CREATE TRIGGER after_return_book
                    AFTER UPDATE ON borrow_records
                    FOR EACH ROW
                    BEGIN
                        IF NEW.returned = TRUE AND OLD.returned = FALSE THEN
                            UPDATE books 
                            SET available = TRUE 
                            WHERE id = NEW.book_id;
                        END IF;
                    END
                """;
                jdbcTemplate.execute(createTrigger);
                System.out.println("已创建还书触发器");
                
                System.out.println("数据库初始化完成！");
            } catch (Exception e) {
                System.err.println("数据库初始化失败：");
                e.printStackTrace();
                throw e;
            }
        };
    }
} 